package com.augurit.gzcgwsp.excelimportexport.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import jxl.write.NumberFormats;;
/**
 * excel工具类
 * @author 张军
 *
 */
public class ExcelUtil {

	/**读取Excel文件的内容
	 * @param file  待读取的文件
	 * @return
	 */
	public static String readExcel(File file) {
		StringBuffer sb = new StringBuffer();
		Workbook wb = null;
		try {
			//构造Workbook（工作薄）对象
			wb = Workbook.getWorkbook(file);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		if (wb == null)
			return null;

		//获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
		Sheet[] sheet = wb.getSheets();
		if (sheet != null && sheet.length > 0) {
			//对每个工作表进行循环
			for (int i = 0; i < sheet.length; i++) {
				//得到当前工作表的行数
				int rowNum = sheet[i].getRows();
				for (int j = 0; j < rowNum; j++) {
					//得到当前行的所有单元格
					Cell[] cells = sheet[i].getRow(j);
					if (cells != null && cells.length > 0) {
						//对每个单元格进行循环
						for (int k = 0; k < cells.length; k++) {
							//读取当前单元格的值
							String cellValue = cells[k].getContents();
							sb.append(cellValue + "\t");
						}
					}
					sb.append("\r\n");
				}
				sb.append("\r\n");
			}
		}
		//最后关闭资源，释放内存
		wb.close();
		return sb.toString();
	}

	/**
	 * 将上传的文件转换为Workbook
	 * @param InputStream 上传的文件流
	 * @return
	 */
	public static Workbook getWorkBookByInputStream(InputStream is) {
		Workbook wb = null;
		try {
			//构造Workbook（工作薄）对象
			wb = Workbook.getWorkbook(is);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;
	}
	
	
	/**生成一个Excel文件
	 * @param fileName  要生成的Excel文件名
	 */
	public static void writeExcel(String fileName) {
		WritableWorkbook wwb = null;
		try {
			//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(new File(fileName));
		} catch (IOException e) {
			e.printStackTrace();
		}
		if (wwb != null) {
			//创建一个可写入的工作表
			//Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
			WritableSheet ws = wwb.createSheet("sheet1", 0);
			//下面开始添加单元格
			for (int i = 0; i < 10; i++) {
				for (int j = 0; j < 5; j++) {
					//这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
					Label labelC = new Label(j, i, "这是第" + (i + 1) + "行，第"
							+ (j + 1) + "列");
					try {
						//将生成的单元格添加到工作表中
						ws.addCell(labelC);
					} catch (RowsExceededException e) {
						e.printStackTrace();
					} catch (WriteException e) {
						e.printStackTrace();
					}

				}
			}
			try {
				//从内存中写入文件中
				wwb.write();
				//关闭资源，释放内存
				wwb.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**搜索某一个文件中是否包含某个关键字
	 * @param file  待搜索的文件
	 * @param keyWord  要搜索的关键字
	 * @return
	 */
	public static boolean searchKeyWord(File file, String keyWord) {
		boolean res = false;
		Workbook wb = null;
		try {
			//构造Workbook（工作薄）对象
			wb = Workbook.getWorkbook(file);
		} catch (BiffException e) {
			return res;
		} catch (IOException e) {
			return res;
		}
		if (wb == null)
			return res;

		//获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了
		Sheet[] sheet = wb.getSheets();
		boolean breakSheet = false;
		if (sheet != null && sheet.length > 0) {
			//对每个工作表进行循环
			for (int i = 0; i < sheet.length; i++) {
				if (breakSheet)
					break;

				//得到当前工作表的行数
				int rowNum = sheet[i].getRows();

				boolean breakRow = false;

				for (int j = 0; j < rowNum; j++) {
					if (breakRow)
						break;
					//得到当前行的所有单元格
					Cell[] cells = sheet[i].getRow(j);
					if (cells != null && cells.length > 0) {
						boolean breakCell = false;
						//对每个单元格进行循环
						for (int k = 0; k < cells.length; k++) {
							if (breakCell)
								break;
							//读取当前单元格的值
							String cellValue = cells[k].getContents();
							if (cellValue == null)
								continue;
							if (cellValue.contains(keyWord)) {
								res = true;
								breakCell = true;
								breakRow = true;
								breakSheet = true;
							}
						}
					}
				}
			}
		}
		//最后关闭资源，释放内存
		wb.close();

		return res;
	}

	/**往Excel中插入图片
	 * @param dataSheet  待插入的工作表
	 * @param col 图片从该列开始
	 * @param row 图片从该行开始
	 * @param width 图片所占的列数
	 * @param height 图片所占的行数
	 * @param imgFile 要插入的图片文件
	 */
	public static void insertImg(WritableSheet dataSheet, int col, int row,
			int width, int height, File imgFile) {
		WritableImage img = new WritableImage(col, row, width, height, imgFile);
		dataSheet.addImage(img);
	}

	/**
	 * 复制旧excel到新工作区
	 * @param inFile
	 * @param outFile
	 * @param index
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 */
	public static WritableWorkbook createWorkbookByOld(String inFile,
			String outFile) throws BiffException, IOException {
		File file1 = new File(inFile);
		File file2 = new File(outFile);
		Workbook wb = Workbook.getWorkbook(file1);
		WritableWorkbook wwb = Workbook.createWorkbook(file2, wb);
		if (wwb == null)
			return null;
		return wwb;
	}

	public static WritableSheet getWritableSheetByIndex(WritableWorkbook wwb,
			int index) {
		if (wwb == null)
			return null;
		return wwb.getSheet(index);
	}

	/** 根据名称获取 sheet
	 * @param wwb
	 * @param columnName sheet 的名称
	 * @return
	 */
	public static WritableSheet getWritableSheetByIndex2(WritableWorkbook wwb,
			String columnName) {
		if (wwb == null)
			return null;
		return wwb.getSheet(columnName);
	}
	
	/** 根据名称获取 sheet
	 * @param wwb
	 * @param SheetName
	 * @return
	 */
	public static WritableSheet getWritableSheetBySheetName(WritableWorkbook wwb,
			String SheetName) {
		if (wwb == null)
			return null;
		return wwb.getSheet(SheetName);
	}

	
	public static void setCell(WritableSheet dataSheet, WritableCell cell, String Value, CellFormat format ) throws RowsExceededException, WriteException{
		//判断单元格的类型, 做出相应的转化
		if (cell.getType() == CellType.LABEL) {
			Label lbl = (Label) cell;
			lbl.setString(Value);
			if (format!= null ) lbl.setCellFormat(format);
		} else if (cell.getType() == CellType.NUMBER) {
			//数字单元格修改
			Value = "".equals(Value.trim()) ? "0" : Value;
			Number n1 = (Number) cell;
			n1.setValue(Double.parseDouble(Value));
			if (format!= null )  n1.setCellFormat(format);
		} else if (cell.getType() == CellType.EMPTY) {
		    Label label = new Label(cell.getColumn(), cell.getRow(), Value);
            if (format!= null ) label.setCellFormat(format);
            dataSheet.addCell(label);
		}else{
		    try {
                Number n1 = new Number(cell.getColumn(), cell.getRow(), Double
                        .parseDouble(Value));
                if (format!= null ) n1.setCellFormat(format);
                dataSheet.addCell(n1);
            } catch (Exception e) {
                Label label = new Label(cell.getColumn(), cell.getRow(), Value);
                if (format!= null ) label.setCellFormat(format);
                dataSheet.addCell(label);
            }
		    
		}
	}
	
	/**
	 * 合并单元格
	 * @param sheet
	 * @param startcolumn 开始列
	 * @param startrow 开始行
	 * @param endcolumn 结束行
	 * @param endrow 结束列
	 */
	public static void mergeCells(WritableSheet sheetWrite,int startcolumn, int startrow,int endcolumn, int endrow){
		try {
			sheetWrite.mergeCells(startcolumn, startrow, endcolumn, endrow);
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 修改某一个单元格的值
	 * @param dataSheet
	 * @param col 列
	 * @param row 行
	 * @param str
	 * @param format
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	
	public static void modiStrCell2(WritableSheet dataSheet, int col, int row,
			String str, CellFormat format) throws RowsExceededException,
			WriteException {
		//获得单元格对象
		WritableCell cell = dataSheet.getWritableCell(col, row);
		setCell(dataSheet, cell, str,format );
	}
	
	
	/**
	 * @param c  单元格的背景色(返回的格式，都是已经加了边框的)
	 * @return
	 * @throws WriteException
	 */
	public static WritableCellFormat getCellFormat(Colour c) throws WriteException {
		WritableCellFormat wcf = new WritableCellFormat();
		wcf.setBackground(c);
		wcf.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
		return wcf;
	}


	/**
	 * 读取单元格的数据
	 * @param dataSheet
	 * @param col 列
	 * @param row 行
	 * @return
	 */
	
	public static String readData(WritableSheet dataSheet, int col, int row) {
		//通过WritableSheet方法的getCell方法选择单元格位置（两个参数都从0开始）
		Cell c = dataSheet.getCell(col, row);
		//通过Cell的getContents方法把单元格中的信息以字符的形式读取出来
		String str = c.getContents();
		return str;
	}

	/**
	 * 读取某一个单元格的数据
	 * @param dataSheet
	 * @param col 列
	 * @param row 行
	 * @return
	 */
	public static String readData(Sheet sheet, int col, int row) {
		//通过WritableSheet方法的getCell方法选择单元格位置（两个参数都从0开始）
		Cell c = sheet.getCell(col, row);
		//通过Cell的getContents方法把单元格中的信息以字符的形式读取出来
		String str = c.getContents().trim();
		return str;
	}
	
	/**
	 * 给单元格添加边框
	 * @param dataSheet WritableSheet : 工作表
	 * @param start int : 要添加多少列边框
	 * @param start_draw int : 从哪个单元格开始添加
	 * @param end int : 要添加多少行边框
	 * @param end_draw int : 到哪个单元结束添加
	 * @return
	 */
	public static void addBorder(int start, int start_draw, int end,
			int end_draw, WritableSheet dataSheet) {
		WritableCellFormat wcf = new WritableCellFormat();
		try {
			wcf.setBorder(jxl.format.Border.ALL,
					jxl.format.BorderLineStyle.THIN);
			for (int i = start_draw; i <= start; i++) {
				for (int j = end_draw; j <= end; j++) {
					//设置对齐方式
					//wcf.setAlignment(Alignment.CENTRE);
					//wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
					//设置边框样式
					Label labelCFC = new Label(i, j, null, wcf);
					dataSheet.addCell(labelCFC);
				}
			}
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 读取一行信息
	 * @param dataSheet WritableSheet : 工作表
	 * @param cellName : 列名
	 * @return
	 */
	public static List readRowData(WritableSheet dataSheet, int k) {
		//通过WritableSheet方法的getCell方法选择单元格位置（两个参数都从0开始）
		Cell[] cells = dataSheet.getRow(k);
		String str = "";
		List list = null;
		//通过Cell的getContents方法把单元格中的信息以字符的形式读取出来
		if (cells != null && cells.length > 0) {
			list = new ArrayList();
			for (int h = 5; h < cells.length; h++) {
				str = cells[h].getContents();
				// System.out.println("str::"+str);
				list.add(str);

			}
		}

		return list;
	}

	/**  
	 * 插入公式  
	 * @param sheet  
	 * @param col  
	 * @param row  
	 * @param formula  
	 * @param format  
	 */
	public static void insertFormula(WritableSheet sheet, Integer col,
			Integer row, String formula, WritableCellFormat format) {
		try {
			Formula f = new Formula(col, row, formula);
			if(format != null)
				f.setCellFormat(format);
			sheet.addCell(f);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**  
	 * 插入超联接  
	 * @param sheet  
	 * @param col  
	 * @param row  
	 * @param formula  
	 * @param format  
	 */
	public static void insertLink(WritableSheet sheet, WritableSheet sheet2,
			Integer col, Integer row, String colum, WritableCellFormat format) {
		try {
			WritableHyperlink link = new WritableHyperlink(col, row, colum,
					sheet2, 1, 1);
			sheet.addHyperlink(link);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**  
	 * 插入超联接  
	 * @param sheet  
	 * @param col  
	 * @param row  
	 * @param formula  
	 * @param format  
	 */
	public static void insertLink(WritableSheet sheet, WritableSheet sheet2,
			Integer col, Integer row, String context, WritableCellFormat format, int destcol, int destrow, int lastdestcol, int lastdestrow) {
		try {
			WritableHyperlink link = new WritableHyperlink(col, row, col, row, context, sheet2, destcol, destrow, lastdestcol, lastdestrow);
			sheet.addHyperlink(link);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	
	
	/**此单元格复制存在问题，用copyRangeBo
	 * @param src 源sheet
	 * @param desc目的Sheet
	 * @param srcStRow
	 * @param srcStCol
	 * @param srcEndRow
	 * @param srcEndCol
	 * @param descStRow
	 * @param descStCol
	 * @param descEndRow
	 * @param descEndCol
	 * @throws Exception
	 */
	public static void copyRange(WritableSheet src,WritableSheet desc,
			int srcStRow, int srcStCol, int srcEndRow, int srcEndCol,  
			int descStRow,int descStCol,int descEndRow,int descEndCol   ) throws Exception {
		if (srcEndRow -srcStRow != descEndRow - descStRow ||srcEndCol - srcStCol != descEndCol - descStCol)
			throw new Exception("复制Excel区域的大小不一致");


		for(int i=srcStRow; i<= srcEndRow; i++){
			for(int j =srcStCol; j <= srcEndCol; j ++){
				copyCell(src,desc,i , j , descStRow + ( i - srcStCol ),descStCol+ (j -srcStCol));
			}
		}
	}
	
	//单元格的范围复制
	public static void copyRangeBo(WritableSheet src,WritableSheet desc,
			int srcStRow, int srcStCol, int srcEndRow, int srcEndCol,  
			int descStRow,int descStCol,int descEndRow,int descEndCol   ) throws Exception {
		if (srcEndRow -srcStRow != descEndRow - descStRow ||srcEndCol - srcStCol != descEndCol - descStCol)
			throw new Exception("复制Excel区域的大小不一致");


		for(int i=srcStRow; i<= srcEndRow; i++){
			for(int j =srcStCol; j <= srcEndCol; j ++){
				copyCell(src,desc,i , j , descStRow + ( i - srcStRow ),descStCol+ (j -srcStCol));
			}
		}
	}
	 /** 
	    * 插入一行数据 
	    * @param sheet       工作表 
	    * @param row         行号 
	    * @param content     内容 
	    * @param format      风格 
	    */ 
	   public static void insertRowData(WritableSheet sheet,Integer row,String[] dataArr,WritableCellFormat format){ 
		   try{   
	           Label label;   
	           for(int i=0;i<dataArr.length;i++){   
	               label = new Label(i,row,dataArr[i],format);   
	               sheet.addCell(label);   
	           }   
	       }catch(Exception e){   
	           e.printStackTrace();   
	       }   
	   }  
	
	/**
	 * 复制单元格
	 * @param sheetIndex 工作薄
	 * @param rowIndex 行下标
	 * @param columnIndex 列下标
	 * @decRowIndex 目的行
	 * @decColIndex 目的列
	 * @return
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public static void copyCell(WritableSheet src,WritableSheet desc,int rowIndex,int columnIndex,int decRowIndex,int decColIndex) 
		throws  RowsExceededException, WriteException{
		WritableCell srcCell = src.getWritableCell(columnIndex, rowIndex);
		WritableCell newCell = srcCell.copyTo(decColIndex, decRowIndex);
        desc.addCell(newCell);
	}


    /**
     * 生成常用的格式
     * @param font 字体
     * @param fontsize 字体大小
     * @param alg 文字对齐的方式
     * @param vag 文字排列的方式
     * @param wrap 是否换行
     * @return
     */
    public static WritableCellFormat createCellFormat(String font,int fontsize,
            Alignment alg,VerticalAlignment  vag,boolean wrap){
         WritableCellFormat wcf=null;
         try {
            WritableFont wfont = new WritableFont(WritableFont.createFont(font),fontsize);
            wcf = new WritableCellFormat(wfont,NumberFormats.TEXT);
            wcf.setAlignment(alg);
            wcf.setVerticalAlignment(vag);
            wcf.setWrap(wrap);
        } catch (Exception e) {
          e.printStackTrace();
        }   
        return wcf;
    }
    
    /**
     * 生成常用的格式
     * @param font 字体
     * @param fontsize 字体大小
     * @param alg 文字对齐的方式
     * @param vag 文字排列的方式
     * @param wrap 是否换行
     * @return
     */
    public static WritableCellFormat createCellFormat2(String font,int fontsize,
            Alignment alg,VerticalAlignment  vag,boolean wrap,Border borderLeft,Border borderRight,Border borderTop,Border borderBottom){
         WritableCellFormat wcf=null;
         try {
        	WritableFont wfont = new WritableFont(WritableFont.createFont(font),fontsize);
            wcf = new WritableCellFormat(wfont,NumberFormats.TEXT);
            wcf.setAlignment(alg);
            wcf.setVerticalAlignment(vag);
            wcf.setWrap(wrap);
            if(borderLeft != null){
            	wcf.setBorder(Border.LEFT,BorderLineStyle.THIN,Colour.BLACK);
            }
            if(borderRight != null){
            	wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN,Colour.BLACK);
            }
            if(borderTop != null){
            	wcf.setBorder(Border.TOP,BorderLineStyle.THIN,Colour.BLACK);
            }
            if(borderBottom != null){
            	wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN,Colour.BLACK);
            }
        } catch (Exception e) {
          e.printStackTrace();
        }   
        return wcf;
    }
	
	
	public static void main(String[] args) throws BiffException, IOException,
			RowsExceededException, WriteException {
		String s = "B5";
		String s2 = "B5";
		String inFile = "E:\\Tomcat 5.5\\webapps\\DzywApp\\statTemplate\\test2.xls";
		WritableWorkbook ww = createWorkbookByOld(inFile, "E:\\test2.xls");
		WritableSheet ws = getWritableSheetByIndex(ww, 0);
		WritableSheet ws2 = getWritableSheetByIndex(ww, 1);

		String starttime = "2008-10-12 10:23:10";
		String endtime = "2008-10-10 10:23:10";

		ww.write();
		ww.close();
		Runtime rt = Runtime.getRuntime();
		rt.exec("C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE  E:\\test2.xls");
	}

	
	

	
}